package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.BankAccount;
import ModelLayer.Building;

public class DBBuilding implements IFDBBuilding {
	
	
	private Connection con = DBConnection.getInstance().getDBcon();
	private static DBBuilding dbBuild;
	
	private DBBuilding(){}

	public static DBBuilding getInstance(){
		if(dbBuild==null) dbBuild = new DBBuilding();
		return dbBuild;
	}
	
	@Override
	public ArrayList<Building> getAll() {
		return miscWhere("");
	}

	@Override
	public Building getBuildingById(int buildingNr) {
		return singleWhere("buildingNr="+buildingNr);
	}
	
	@Override
	public ArrayList<Building> getAll_where(String wClause) {
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Building> searchRandom(String searchF) {
		
		return miscWhere("buildingNr LIKE '"+searchF+"' OR buildingName LIKE '"+searchF+"%' OR address LIKE '"+searchF+"%' OR city LIKE '"+searchF+"%' OR postalcode LIKE '"+searchF+"%' OR country LIKE '"+searchF+"%' OR b.zone LIKE '"+searchF+"%' OR levyAccountNo LIKE '"+searchF+"%' OR rateZoneAmount LIKE '"+searchF+"'");
	}
	
	@Override
	public Building getBuildingByName(String name) {
		return singleWhere("buildingName LIKE '"+name+"%'");
	}

	@Override
	public int insert(Building building) 
	{
		int rc = -1;
		
		
		// START Location 
		int locationId =  DBLocation.getInstance().insert(building.getPostalcode(), building.getCity(), building.getCountry());
		// END Location
					
		// START BankDetails
		rc = DBBankAccount.getInstance().insert(new BankAccount(building.getLevyAccount().getAccountNo(), building.getLevyAccount().getAccountName(), building.getLevyAccount().getBankName(), building.getLevyAccount().getBranchCode()));
		// END BankDetails
		
		String query = "INSERT INTO Building (buildingName,address,zone,locationId,levyAccountNo,companyName) "
					 + "VALUES ('"+building.getBuildingName()+"','"+building.getAddress()+"','"+building.getZone()+"',"+locationId+",'"+building.getLevyAccount().getAccountNo()+",'"+building.getCompanyName()+"')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the Building "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int update(Building building) 
	{
		int rc = -1;
		
		// START Location 
		int locationId =  DBLocation.getInstance().insert(building.getPostalcode(), building.getCity(), building.getCountry());
		// END Location
							
		// START BankDetails
		rc = DBBankAccount.getInstance().insert(new BankAccount(building.getLevyAccount().getAccountNo(), building.getLevyAccount().getAccountName(), building.getLevyAccount().getBankName(), building.getLevyAccount().getBranchCode()));
		// END BankDetails
	
		String query = "UPDATE Building SET buildingName = '"+building.getBuildingName()+"', address = '"+building.getAddress()+"', zone = '"+building.getZone()+"', locationId = "+locationId+", levyAccountNo = '"+building.getLevyAccount().getAccountNo()+"', companyName = '"+building.getCompanyName()+"' WHERE buildingNr="+building.getBuidlingNr();
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the Building "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int delete(int apartId) {
		int rc = -1;
		String query = "DELETE FROM Building WHERE apartId="+apartId;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the Building "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	private Building singleWhere(String wClause){
		Building building = new Building();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				building = buildBuilding(result);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select Building query "+e.getMessage());
			System.out.println(query);
		}
		
		return building;
	}
	
	
	private ArrayList<Building> miscWhere(String wClause){
		ArrayList<Building> apartments = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				apartments.add(buildBuilding(results));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Building query "+e.getMessage());
		}
		
		return apartments;
	}
	
	
	private String buildQuery(String wClause){
		String query = "SELECT buildingNr,buildingName,address,b.zone,rateZoneAmount,levyAccountNo,companyName,postalCode,city,country "
					 + "FROM Building b LEFT JOIN Location loc ON b.locationId=loc.locationId "
					 + "LEFT JOIN RateZoneValue rza ON b.zone=rza.zone";
		
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	
	private Building buildBuilding(ResultSet result){
		Building building = new Building();
		
		try{
			building.setBuidlingNr(result.getInt("buildingNr"));
			building.setBuildingName(result.getString("buildingName"));
			building.setAddress(result.getString("address"));
			building.setZone(result.getString("zone"));
			
			building.setPostalcode(result.getString("postalCode"));
			building.setCity(result.getString("city"));
			building.setCountry(result.getString("country"));
			
			building.setRateZoneAmount(result.getDouble("rateZoneAmount"));
			building.setCompanyName(result.getString("companyName"));
			
			building.setLevyAccount(DBBankAccount.getInstance().getBankAccount(result.getString("levyAccountNo")));
		}
		catch(Exception e){
			System.err.println("Could not build the Building Result "+e.getMessage());
		}
		
		return building;
	}
	
	
}
